import pandas as pd
import pynysqi
import pymysql.cursors
import numpy as np
import confi import CITY_DICT

class mysqlutilst(object):
    """
    这库工具类
    """
    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='12345',
            database='scenic',
            port=3306,
            charset='utf8'
        )

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql="""
        SELECT LEFT(u.id_no, 4) as city_code, DATE_FORMAT(o.create_time,"%Y-%m') as month,count(u.id) as visitor_count FROM ticket_order_user_rel u
        JOIN ticket_order o on o.id = u.order_id WHERE LENGTH(u.id_no) = 18 ando.pay_time is not null and o.pay_time !='' GROUP BY city_code, month

        """   

        cursor.execute(sql)
        ret = cursor.fetchall()
        # df = pd.DataFrame(ret)
        # print(df.head)
        new_list= []
        for item in ret:
                if item['city_code'] not in CITY_DICT:
                    continue
                new_list.append({
                    'city_code': item['city_code'],
                    'month':item['month'],
                    'visitor_count':item['visitor_count'],
                    'city_name':CITY_DICT[item['city_code']]
                })
        df_city_monthly = pd.DataFrame(new_list)
        df_city_monthly['month']- pd.to_datetime(df_city_monthly['month'] +"-01")
        #假设当前月是2024-12
        def calculate_baseline(df,current_month, window_size = 6):
        #提前当月数据
            df_current = df[df['month'] -current_month]
        #提取历史数据
            history_start = current_month - pd.Dateoffset(month=window_size)
            df_history = df[(df['bnth']>history_start) & (df['month'] < current_month)]
        #按城市计算均值和标准差
            df_baseline= df_history = groupby('city_name')['visitor_count'].agg(['mean','std']).reset_index()
            df_baseline.rename(columns={'mean': 'hist_mean', 'std':'hist_std'},inplace = True)
        #合并当前月份数据
            df_merged  =  df_current.merge(df_baseline, on='city_name', how='left')
            return df_merged
        
        current_month = pd.to_datetime('2024-12-01')
        df_merged = calculate_baseline(df_city_monthly, current_month)
        #计算z-score
        df_merged['z_score']  =  (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']
        #标记暴增暴跌的城市（z_score>3orz_score<-3)
        df_increased = df_merged[df_merged['z_score']>3]
        df_reduce = df_merged[df_merged['z_score']<-3]
        print(df_increased)
        print('------------------------')
        print(df_reduce)


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()
